package cn.lanqiao.bigDate.mapper.data;

import cn.lanqiao.bigDate.entity.data.DataBookOrder;
import cn.lanqiao.bigDate.reslut.DataBookOrderProcessing;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.sql.ResultSet;
import java.util.List;
import java.util.Map;


@Mapper
public interface DataBookOrderMapper extends BaseMapper<DataBookOrder> {
    /**
     * 查找当月销量前5的书的isbn与quantity
     * @param date
     * @return
     */
    @Select("SELECT isbn,SUM(quantity) as quantity " +
            "FROM `data_book_order` " +
            "WHERE date like '%${date}%' " +
            "GROUP BY isbn  " +
            "ORDER BY SUM(quantity) DESC " +
            "limit 5")
//    @Select("select * from `data_book_order`")
    List<Map<String, String>> findBookOrderIsbnAndQuantity(String date);

    /**
     * 根据isbn查询书籍
     * @param isbn
     * @return
     */
    @Select("SELECT * from `data_book` where isbn = #{isbn}")
    Map<String, String> findBookByIsbn(String isbn);

    /**
     * 根据id查找作者
     * @param authorId
     * @return
     */
    @Select("SELECT * from `data_author` where id = #{authorId}")
    Map<String, String>  findAuthorById(String authorId);

    /**
     * 根据id查找出版社
     * @param pbhouseId
     * @return
     */
    @Select("SELECT * from `data_pbhouse` where id = #{pbhouseId}")
    Map<String, String> findPbhouseById(String pbhouseId);

    /**
     * 查找各地区销售码洋和地区
     * @return
     */
    @Select("SELECT b.name,(price*quantity) as Hot  FROM `data_book_order` a , `data_book`  b WHERE a.isbn=b.isbn ")
    ResultSet findRegionsHot();

    /**
     * 查询畅销榜
     * @return
     */
    @Select("SELECT book1.*, left(book1.date, 7) as month\n" +
            "FROM data_book_order book1\n" +
            "where 10>(\n" +
            "\t\t\tSELECT COUNT(1)\n" +
            "\t\t\tFROM data_book_order book2\n" +
            "\t\t\tWHERE  left(book1.date, 7)=left(book2.date, 7) and book1.quantity>book2.quantity\n" +
            "\t\t\t)\n" +
            "ORDER BY left(book1.date, 7)")
    List<DataBookOrder> selectOrderGroupyMonth();


    /**
     * 查找每个月的销售洋马
     * @return
     */
    @Select("SELECT ROUND(SUM(quantity*price/100)) AS money " +
            "FROM `data_book_order`   " +
            "GROUP BY MONTH(date) " +
            "order BY  MONTH(date) ")
    List<Integer>  findMarketData();
    /**
     * 查找月份,价格和数量
     * @return
     */
    @Select("SELECT DATE_FORMAT(date,'%m') as month,(SUM(quantity)*FLOOR(AVG(price))/1000) as total FROM `data_book_order` WHERE left(date,4)=left(NOW(),4) GROUP BY DATE_FORMAT(date,'%m') ORDER BY  month ")
    List<DataBookOrderProcessing> findRegions();
    /**
     * 查找分类销售数量
     * @return
     */
    @Select("SELECT dl.label_name,sum(dbo.quantity) as quantity from data_book_order dbo,data_label dl,data_book_label_heading dbl where dl.id=dbl.label_id and dbo.isbn=dbl.isbn GROUP BY dl.label_name")
    List<Map<String,String>> findSortSellQuantity();

    /**
     * 分类库存数量
     * @return
     */
    @Select("SELECT dl.label_name,sum(ds.stock) as stock from data_stock ds,data_label dl,data_book_label_heading dblh where dl.id=dblh.label_id and ds.isbn=dblh.isbn GROUP BY dl.label_name")
    List<Map<String, String>> sortStockQuantity();

}
